package cn.org.timespace.ons.dao.base.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;

import cn.org.timespace.ons.action.base.MachinesApplyModel;
import cn.org.timespace.ons.dao.TSBaseDAO;
import cn.org.timespace.ons.dao.base.MachinesApplyDao;
import cn.org.timespace.ons.util.pager.Pager;
import cn.org.timespace.ons.vo.base.AppleServer;
import cn.org.timespace.ons.vo.base.MachinesApply;
import cn.org.timespace.ons.vo.base.Options;

public class MachinesApplyDaoImpl extends TSBaseDAO implements MachinesApplyDao {

	@Override
	public void delete(MachinesApply persistentInstance) {
		log.debug("deleting MachinesApply instance");
		try {
			getHibernateTemplate().delete(persistentInstance);
			log.debug("delete successful");
		} catch (RuntimeException re) {
			log.error("delete failed", re);
			throw re;
		}

	}

	
	@Override
	public List<MachinesApply> findByCondition(MachinesApplyModel model) {
		// TODO Auto-generated method stub
		StringBuffer queryString = new StringBuffer("from MachinesApply m where 1=1");

		if (!StringUtils.isEmpty(model.getMaid())) {
			queryString.append(" and m.maid like '%" + model.getMaid()
					+ "%'");
		}
		if (!StringUtils.isEmpty(model.getAsid())) {
			queryString.append(" and m.asid = '" + model.getAsid()
					+ "'");
		}
		if (!StringUtils.isEmpty(model.getMsid())) {
			queryString.append(" and m.msid = '" + model.getMsid()
					+ "'");
		}

		queryString.append(" order by m.maid desc");

		return getHibernateTemplate().find(queryString.toString());
	}

	@Override
	public List<Object> findByCondition(MachinesApplyModel model, Pager p) {
		
		Session session = null;
		Query query = null;
		try {
			StringBuffer queryString = new StringBuffer("from MachinesApply m where 1=1 ");
			
			if (!StringUtils.isEmpty(model.getMaid())) {
				queryString.append(" and m.msid like '%" + model.getMsid()
						+ "%'");
			}
			
			
			
			queryString.append(" order by m.maid desc ");
			
			session = getSessionFactory().openSession();
			query = session.createQuery(queryString.toString());
			
			
			List<Object> resList = new ArrayList();
			resList.add(query.list().size());
			
			setQueryPager(query, p);
			resList.add(query.list());
			
			return resList;
			
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}finally{
			closeSessionAndQuery(session, query);
		}
	}
	
	/**
	 * 根据机器Id查询出所对应的应用
	 */
	@Override
	public List<Object> findByMachineId(String msid , Pager p) {
		// TODO Auto-generated method stub
		
		Session session = null;
		Query query = null;
		List<Object> list = new ArrayList();
		
		try{
			StringBuffer queryString = new StringBuffer("from MachinesApply m ");
			queryString.append(" where 1=1");
				queryString.append(" and m.msid ='");
				queryString.append(msid);
				queryString.append("'");
				queryString.append(" and m.status in(1,2,0)");//0 审核通过 1未审核 2 审核不通过3取消
				queryString.append(" order by m.maid  ");
			session = getSessionFactory().openSession();
			query = session.createQuery(queryString.toString());
				
			session = getSessionFactory().openSession();
			query = session.createQuery(queryString.toString());
			
			
			list.add(query.list().size());
			setQueryPager(query, p);
			list.add(query.list());
			
			
		}catch(RuntimeException re){
			re.printStackTrace();
		}
		return list;
		
	}

	@Override
	public MachinesApply findById(String maid) {
		log.debug("getting MachinesApply instance with id: " + maid);
		try {
			MachinesApply instance = (MachinesApply) getHibernateTemplate().get(
					"cn.org.timespace.ons.vo.base.MachinesApply", maid);
			return instance;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}

	@Override
	public boolean save(MachinesApply transientInstance) {
		log.debug("saving MachinesApply instance");
		boolean flag = false;
		try {
			getHibernateTemplate().save(transientInstance);
			log.debug("save successful");
			flag = true;
		} catch (RuntimeException re) {
			log.error("save failed", re);
			re.printStackTrace();
			throw re;
		}
		return flag;

	}

	/**
	 * 
	 */
	@Override
	public void update(MachinesApply instance) {
		try {
			getHibernateTemplate().update(instance);
		} catch (RuntimeException re) {
			log.error("update failed", re);
			throw re;
		}

	}

	//列出我的申请
	@Override
	public List<Object> findByStartName(String startName , Pager p) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		
		List<Object> resList= new ArrayList();
		
		try{
			StringBuffer queryString = new StringBuffer(" select ma.maid,ma.status,ma.startUserName,ma.endUserName,m.name as msidName,app.name as asidName" +
					" from MachinesApply as ma , Machines as m , AppleServer as app" +
					" where ma.startUserName = '" + startName +"'" +
							" and ma.status in(1,2,0)" +//0 审核通过 1未审核 2 审核不通过3取消
							" and m.mid = ma.msid" +
							" and app.asid = ma.asid");
			session = getSessionFactory().openSession();
			query = session.createQuery(queryString.toString());
			resList.add(query.list().size());
			setQueryPager(query, p);
			List list = new ArrayList();
			for(int i = 0;i < query.list().size();i++){
				Object[] obj=(Object[])query.list().get(i);
				MachinesApply machinesApply = new MachinesApply();
				machinesApply.setMaid(obj[0].toString());
				machinesApply.setStatus(Integer.valueOf(obj[1].toString()));
				machinesApply.setStartUserName(obj[2].toString());
				machinesApply.setEndUserName(obj[3].toString());
				machinesApply.setMsidName(obj[4].toString());
				machinesApply.setAsidName(obj[5].toString());
				list.add(machinesApply);
			}
			resList.add(list);
		}catch(RuntimeException re){
			re.printStackTrace();
		}
		
		return resList;
	}
	//根据条件列出我的申请或者审核
	public List<Object> findApplyAndAuditByCondition(MachinesApply model , Pager p) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		
		List<Object> resList= new ArrayList();
		
		try{
			StringBuffer queryString = new StringBuffer(" select ma.maid,ma.status,ma.startUserName,ma.endUserName,m.name as msidName,app.name as asidName" +
					" from MachinesApply as ma , Machines as m , AppleServer as app" +
					" where 1=1" +
							//" and ma.status in(1,2,0)" +//0 审核通过 1未审核 2 审核不通过3取消
							" and m.mid = ma.msid" +
							" and app.asid = ma.asid");
			//从session里获取用户赋值给发起人列出我的申请
			if (!StringUtils.isEmpty(model.getStartUserName())) {
				queryString.append(" and  ma.startUserName = '" + model.getStartUserName() +"'");
				queryString.append(" and ma.status in(1,2,0)");//0 审核通过 1未审核 2 审核不通过3取消
			}
			//从session里获取用户赋值给审核人列出我的审核
			if (!StringUtils.isEmpty(model.getEndUserName())) {
				queryString.append(" and  ma.endUserName = '" + model.getEndUserName() +"'");
				queryString.append(" and ma.status = 1");//0 审核通过 1未审核 2 审核不通过3取消
			}
			
			queryString.append(" order by ma.maid  ");
			session = getSessionFactory().openSession();
			query = session.createQuery(queryString.toString());
			resList.add(query.list().size());
			setQueryPager(query, p);
			List list = new ArrayList();
			for(int i = 0;i < query.list().size();i++){
				Object[] obj=(Object[])query.list().get(i);
				MachinesApply machinesApply = new MachinesApply();
				machinesApply.setMaid(obj[0].toString());
				machinesApply.setStatus(Integer.valueOf(obj[1].toString()));
				machinesApply.setStartUserName(obj[2].toString());
				machinesApply.setEndUserName(obj[3].toString());
				machinesApply.setMsidName(obj[4].toString());
				machinesApply.setAsidName(obj[5].toString());
				list.add(machinesApply);
			}
			resList.add(list);
		}catch(RuntimeException re){
			re.printStackTrace();
		}
		
		return resList;
	}
	
	/**
	 * 根据应用平台查找信息平台
	 */
	@Override
	public List<Object> findByApplyId(String asid , Pager p) {
		// TODO Auto-generated method stub
		
		Session session = null;
		Query query = null;
		List<Object> list = new ArrayList();
		
		try{
			StringBuffer queryString = new StringBuffer("from MachinesApply m ");
			queryString.append(" where 1=1");
				queryString.append(" and m.asid ='");
				queryString.append(asid);
				queryString.append("'");
				queryString.append(" and m.status in(1,2,0)");//0 审核通过 1未审核 2 审核不通过3取消
				queryString.append(" order by m.maid  ");
			session = getSessionFactory().openSession();
			query = session.createQuery(queryString.toString());
			
			
			list.add(query.list().size());
			setQueryPager(query, p);
			list.add(query.list());
			
			
		}catch(RuntimeException re){
			re.printStackTrace();
		}
		return list;
		
	}

	//根据信息终端ID和发起人，查义出我的信息终端对应的应用
	@Override
	public List<AppleServer> findByMsidAndName(String id, String name, Pager p) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		List<AppleServer> resList = new ArrayList();
//		List<Options> resList = new ArrayList();
		try{
//			StringBuffer queryString = new StringBuffer(" select  app.name,app.address,app.port from MachinesApply m ,AppleServer app where app.asid=m.asid ");
			StringBuffer queryString = new StringBuffer(" select  app from MachinesApply m ,AppleServer app where app.asid=m.asid ");
				queryString.append(" and m.msid ='");//根据信息终端Id查找
				queryString.append(id);
				queryString.append("'");
				queryString.append(" and m.status = 0 ");//0 审核通过 
				
				//根据名称查找
				if(!StringUtils.isEmpty(name)){
					queryString.append(" and app.name like '%");
					queryString.append(name.trim());
					queryString.append("%'");
				}
				
				queryString.append(" order by m.maid  ");
			session = getSessionFactory().openSession();
			query = session.createQuery(queryString.toString());
			
//			for(int i = 0;i < query.list().size();i++){
//				Object[] obj=(Object[])query.list().get(i);
//				Options options = new Options();
//				options.setTheId(obj[0].toString());
//				options.setName(obj[1].toString());
//				options.setCode(obj[2].toString());
//				
//				resList.add(options);
//			}
			resList = query.list();
			
		}catch(RuntimeException re){
			re.printStackTrace();
		}
		return resList;
	}


	@Override
	public List<Options> findByAppIdAndName(String asid, String name, Pager p) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		List<Options> resList = new ArrayList();
		
		try{
			StringBuffer queryString = new StringBuffer("select eu.compName, ma.name,ma.remark  from MachinesApply m ,Machines ma,EfUser eu");
			queryString.append(" where 1=1");
				queryString.append(" and m.asid ='"+asid.trim()+"' and m.status =0 and  m.msid = ma.mid and ma.userName = eu.userAccount");
				
//				queryString.append(" and m.status = 0 ");//0 审核通过
				//名称条件查询
				if(!StringUtils.isEmpty(name)){
					queryString.append(" and ma.name like '%");
					queryString.append(name.trim());
					queryString.append("%'");
				}
				
				
				queryString.append(" order by m.maid");
				
				
				System.out.println("SQL为："+queryString.toString());
			session = getSessionFactory().openSession();
			query = session.createQuery(queryString.toString());
			for(int i = 0;i < query.list().size();i++){
				Object[] obj=(Object[])query.list().get(i);
				Options options = new Options();
				options.setTheId(obj[0].toString());
				options.setName(obj[1].toString());
				options.setCode(obj[2].toString());
				resList.add(options);
			}
			
			
		}catch(RuntimeException re){
			re.printStackTrace();
		}
		return resList;
	}
	
	//查询用户应用类型已审核的应用
	public List<Object> findByApplyByAudit(String msid,String theIdMachine,String userName) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		List<Object> resList = new ArrayList();
		
		try{
			StringBuffer queryString = new StringBuffer(" select app.asid ,app.name ,app.status,app.interType,app.interAddress ,app.interMethod,app.address from AppleServer app, UserMachineType umt,TypeAudit taudit");
			queryString.append(" where 1=1");
				
				queryString.append(" and app.status = 1 ");//是否上线标志
				
				queryString.append(" and taudit.status = 0  ");//0审核通过
				queryString.append(" and taudit.theIdmachine = umt.theIdMachine  ");
				queryString.append(" and app.asid = taudit.theIdapply  ");
				
				queryString.append(" and umt.theIdMachine = '"+theIdMachine+"'  ");//信息终端分类主键，在页面是从下拉框获取
				queryString.append(" and taudit.startUserName = '"+userName+"'  ");//发起人是登陆用户
				queryString.append(" and app.asid not in( select app.asid  from AppleServer app,MachinesApply mapp  where " +
						"  app.status = 1");//已添加的应用不列出来
				queryString.append(" and app.status = 1 ");//是否上线标志
				queryString.append(" and app.asid=mapp.asid ");
				queryString.append(" and mapp.startUserName = '"+userName+"'  ");//发起人是登陆用户
				queryString.append(" and mapp.msid = '"+msid+"'  ");//信息终端ID
				queryString.append("   ");queryString.append(" and mapp.status = 0  ");//已审核
				queryString.append(" ) ");
				queryString.append(" order by app.asid  ");
				
				
			session = getSessionFactory().openSession();
			query = session.createQuery(queryString.toString());
			//List list = new ArrayList();
			for(int i = 0;i < query.list().size();i++){
				Object[] obj=(Object[])query.list().get(i);
				AppleServer appServer = new AppleServer();
				appServer.setAsid(obj[0].toString());
				appServer.setName(obj[1].toString());
				appServer.setStatus(Integer.valueOf(obj[2].toString()));
				appServer.setInterType(obj[3].toString());
				appServer.setInterAddress(obj[4].toString());
				appServer.setInterMethod(obj[5].toString());
				appServer.setAddress(obj[6].toString());
				resList.add(appServer);
			}
			
		}catch(RuntimeException re){
			re.printStackTrace();
		}
		return resList;
	}
	
	//查询信息终端已选择的应用
	public List<Object> findByApplyByAsAudit(String mid,String theIdMachine) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		List<Object> resList = new ArrayList();
		
		try{
			StringBuffer queryString = new StringBuffer(" select app.asid ,app.name ,app.status,app.interType,app.interAddress ,app.interMethod,app.address from AppleServer app,MachinesApply mapp,Machines ma,UserMachineType umt ");
			queryString.append(" where 1=1");
				
				queryString.append(" and app.status = 1 ");//是否上线标志
				queryString.append(" and app.asid=mapp.asid ");
				queryString.append(" and mapp.status = 0  ");//已审核
				
				queryString.append(" and mapp.msid='"+mid+"'");
				queryString.append(" and mapp.msid=ma.mid");
				queryString.append(" and ma.theIdMachine=umt.theIdMachine");
				queryString.append(" and umt.theIdMachine='"+theIdMachine+"'");
				queryString.append(" order by app.asid  ");
				
				
				
			session = getSessionFactory().openSession();
			query = session.createQuery(queryString.toString());
			//List list = new ArrayList();
			for(int i = 0;i < query.list().size();i++){
				Object[] obj=(Object[])query.list().get(i);
				AppleServer appServer = new AppleServer();
				appServer.setAsid(obj[0].toString());
				appServer.setName(obj[1].toString());
				appServer.setStatus(Integer.valueOf(obj[2].toString()));
				appServer.setInterType(obj[3].toString());
				appServer.setInterAddress(obj[4].toString());
				appServer.setInterMethod(obj[5].toString());
				appServer.setAddress(obj[6].toString());
				resList.add(appServer);
			}
			
			
			
			
		}catch(RuntimeException re){
			re.printStackTrace();
		}
		return resList;
	}

	//根据信息终端ID删除
	public void deleteByMachinesId(String  msid) {
		
		log.debug(" deleteing MachinesApply instance");
		Session session = null;
		Query query = null;
		try {
			//纯sql
			StringBuffer queryString = new StringBuffer("delete from machinesapply  where msid='"+msid+"'");
			session = getSessionFactory().openSession();
			query = session.createSQLQuery(queryString.toString());
			query.executeUpdate();
			log.debug("delete successful");
			
		} catch (RuntimeException re) {
			log.error("delete failed", re);
			throw re;
		}
	}
	
	@Override
	public ArrayList<AppleServer> findAppleServerByMachinesID(String machinesID) {
		String hql = "select s from MachinesApply ms,Machines m,AppleServer s " +
				" where m.machinesID ='" + machinesID + "'" +
			    " and m.mid=ms.msid" +
			    " and s.asid = ms.asid";
		try {
			return (ArrayList) getHibernateTemplate().find(hql);
		} catch (RuntimeException re) {
			re.printStackTrace();
			log.error("find all failed", re);
			throw re;
		}

	}
	
	//查询用户应用类型已审核的应用
	public List<Object> findByApplyByAuditNew(String theIdMachine,String userName) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		List<Object> resList = new ArrayList();
		
		try{
			StringBuffer queryString = new StringBuffer(" select app.asid ,app.name ,app.status,app.interType,app.interAddress ,app.interMethod,app.address from AppleServer app, UserMachineType umt,TypeAudit taudit");
			queryString.append(" where 1=1");
				
				queryString.append(" and app.status = 1 ");//是否上线标志
				
				queryString.append(" and taudit.status = 0  ");//0审核通过
				queryString.append(" and taudit.theIdmachine = umt.theIdMachine  ");
				queryString.append(" and taudit.theIdapply = app.asid  ");
				queryString.append(" and umt.theIdMachine = '"+theIdMachine+"'  ");//信息终端分类主键，在页面是从下拉框获取
				queryString.append(" and taudit.startUserName = '"+userName+"'  ");//发起人是登陆用户
				queryString.append(" and app.status = 1 ");//是否上线标志


				queryString.append(" order by app.asid  ");
				
				
			session = getSessionFactory().openSession();
			query = session.createQuery(queryString.toString());
			//List list = new ArrayList();
			for(int i = 0;i < query.list().size();i++){
				Object[] obj=(Object[])query.list().get(i);
				AppleServer appServer = new AppleServer();
				appServer.setAsid(obj[0].toString());
				appServer.setName(obj[1].toString());
				appServer.setStatus(Integer.valueOf(obj[2].toString()));
				appServer.setInterType(obj[3].toString());
				appServer.setInterAddress(obj[4].toString());
				appServer.setInterMethod(obj[5].toString());
				appServer.setAddress(obj[6].toString());
				resList.add(appServer);
			}
			
		}catch(RuntimeException re){
			re.printStackTrace();
		}
		return resList;
	}


	@Override
	public String findByMsid(String id) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		List<Options> resList = new ArrayList();
		
		try{
			StringBuffer queryString = new StringBuffer(" select  app.name,app.address,app.port from MachinesApply m ,AppleServer app where app.asid=m.asid ");
			
				queryString.append(" and m.msid ='");//根据信息终端Id查找
				queryString.append(id);
				queryString.append("'");
				queryString.append(" and m.status = 0 ");//0 审核通过 
				
				
				queryString.append(" order by m.maid  ");
			session = getSessionFactory().openSession();
			query = session.createQuery(queryString.toString());
			
			
			
			
		}catch(RuntimeException re){
			re.printStackTrace();
		}
		String lengString = query.list().size()+"";
		return lengString;
	}


	@Override
	public String findByAsid(String asid) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		List<Options> resList = new ArrayList();
		
		try{
			StringBuffer queryString = new StringBuffer("select ma.compCode, ma.name,ma.remark  from MachinesApply m ,Machines ma");
			queryString.append(" where 1=1");
				queryString.append(" and m.asid ='");
				queryString.append(asid);
				queryString.append("'");
				queryString.append(" and m.status = 0 ");//0 审核通过
				queryString.append(" and  m.msid = ma.mid ");
				queryString.append(" order by m.maid  ");
			session = getSessionFactory().openSession();
			query = session.createQuery(queryString.toString());
		}catch(RuntimeException re){
			re.printStackTrace();
		}
		String lengString = query.list().size() + "";
		return lengString;
	}
}
